﻿
Imports System.IO
Imports System.Net
Imports System.Net.Http
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports System.Windows.Forms.ListBox



Public Class Form1
    'Private ReadOnly urlColumn As Integer = 1 ' 存放链接的列数，从1开始计数
    'Private ReadOnly urlName As Integer = 1 ' 存放链接的列数，从1开始计数
    'Private ReadOnly saveFolder As String = "F:\Downloads" ' 下载文件保存的文件夹路径
    Private ReadOnly maxThreads As Integer = 15 ' 最大线程数



    Private Shared Async Function DownloadFileAsync(url As String, savePath As String) As Task
        ' 按照链接下载图片
        Try
            ' 创建HttpClient并下载文件
            Using client As New HttpClient()
                Dim response = Await client.GetAsync(url)
                response.EnsureSuccessStatusCode()
                Using contentStream = Await response.Content.ReadAsStreamAsync(),
                  stream = New FileStream(savePath, FileMode.Create, FileAccess.Write, FileShare.None, bufferSize:=4096, useAsync:=True)
                    Await contentStream.CopyToAsync(stream)
                End Using

            End Using
        Catch ex As Exception
            'MessageBox.Show("Failed to download file: " & url & vbCrLf & ex.Message)

            Debug.Print("Failed to download file: " & Now() & vbTab & savePath & vbTab & url & vbTab & ex.Message)

        End Try
    End Function





    Private Shared Sub ReleaseComObject(obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
        End Try
    End Sub


    Private Sub Form1_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
        ' 停止程序并返回 0 表示正常退出
        Environment.Exit(0)

    End Sub

    Private Sub BtnBrowse_Click(sender As Object, e As EventArgs) Handles BtnBrowse.Click
        ' 打开文件选择对话框
        Dim openFileDialog As New OpenFileDialog With {
            .Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*",
            .FilterIndex = 1,
            .RestoreDirectory = True
        }

        If openFileDialog.ShowDialog() = DialogResult.OK Then
            ' 显示选择的文件路径
            txtFilepath.Text = openFileDialog.FileName
        End If
    End Sub

    Private Sub BtnFolder_Click(sender As Object, e As EventArgs) Handles BtnFolder.Click
        ' 设置对话框的说明信息和根文件夹
        Dim folderBrowserDialog1 As New FolderBrowserDialog With {
            .Description = "请选择文件夹",
            .RootFolder = Environment.SpecialFolder.Desktop
        }
        ' 如果用户按下了“确定”按钮，则将所选文件夹路径显示在文本框中
        If folderBrowserDialog1.ShowDialog() = DialogResult.OK Then
            TbFolder.Text = folderBrowserDialog1.SelectedPath
        End If
    End Sub

    Private Sub BtnDownload_Click(sender As Object, e As EventArgs) Handles BtnDownload.Click
        Dim saveFolder As String = Me.TbFolder.Text
        Dim SleepNum As Integer = Val(Me.TbSleep.Text)
        Dim ColForUrl As Integer = Val(Me.TbColUrl.Text)
        Dim ColForName As Integer = Val(Me.TbColName.Text)
        Dim colForInsert As Integer = Val(Me.TbInsertName.Text)

        If Directory.Exists(saveFolder) = False Then
            saveFolder = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\exp\" _
            & Replace(Microsoft.VisualBasic.Format(Now, "yyyy-MM-dd hh_mm_ss"), " ", "\")

            Me.TbFolder.Text = saveFolder

        End If
        saveFolder = saveFolder & "/" & Path.GetFileName(txtFilepath.Text)
        Directory.CreateDirectory(saveFolder) ' 创建文件夹

        '随机数
        If SleepNum = 0 Then
            Dim random As New Random()
            SleepNum = random.Next(0, 1001) ' 生成0到1000之间的随机数
            Me.TbSleep.Text = SleepNum
        End If

        If ColForUrl = 0 Then
            ColForUrl = 1
        End If
        ' 读取Excel文件
        Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(txtFilepath.Text)
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = xlWorkBook.Worksheets(1)
        Dim lastRow As Integer = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell).Row
        Dim sha As Microsoft.Office.Interop.Excel.Shape
        Dim l#, t#, w#, h#
        Dim nFile As String
        Dim fileDic As New Dictionary(Of Integer, String)
        Dim maxRowHeight As Double = 409

        nFile = System.IO.Path.Combine(saveFolder, xlWorkBook.Name)
        If File.Exists(nFile) Then
            nFile = System.IO.Path.Combine(saveFolder, Microsoft.VisualBasic.Format(Now, "hh_mm_ss") + xlWorkBook.Name)
        End If

        ' 设置进度条
        ProgressBar.Minimum = 0
        ProgressBar.Maximum = lastRow - 1
        ProgressBar.Value = 0
        ProgressBar.Step = 1

        ' 创建下载任务
        Dim downloadTasks As New List(Of Task)()
        For i As Integer = 1 To lastRow

            ' 下载链接
            Dim urlRng As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.Cells(i, ColForUrl)
            Dim url As String = urlRng.Value

            ' 文件命名
            Dim fileName As String = ""
            If ColForName > 0 Then
                Dim fileRng As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.Cells(i, ColForName)
                fileName = fileRng.Value & ".jpg" ' System.IO.Path.GetFileName(url)
            Else
                fileName = i & ".jpg"
            End If

            Dim savePath As String = System.IO.Path.Combine(saveFolder, fileName)

            fileDic.Add(i, savePath)

            If Not File.Exists(savePath) And Len(url) > 0 Then
                Thread.Sleep(SleepNum)
                downloadTasks.Add(Task.Factory.StartNew(Async Function()
                                                            Await DownloadFileAsync(url, savePath)
                                                        End Function))
            End If

            ' 控制最大线程数
            If downloadTasks.Count >= maxThreads Then
                Task.WaitAll(downloadTasks.ToArray())
                downloadTasks.Clear()
            End If


            ' 更新进度条
            ProgressBar.PerformStep()
            System.Windows.Forms.Application.DoEvents()

            ' 展示进度 百分比
            Me.Label2.Text = i & "/" & lastRow & "(" & Microsoft.VisualBasic.Strings.Format(i / lastRow, "0.00%") & ")"
            With Me.ListBox1

                .Items.Add(savePath)
                .SetSelected(ListBox1.Items.Count - 1, True)
            End With
        Next

        ' 等待所有任务完成
        Task.WaitAll(downloadTasks.ToArray())

        With Me.ListBox1
            .Items.Add($"完成所有文件下载")
            .SetSelected(ListBox1.Items.Count - 1, True)
        End With

        ' 设置进度条
        ProgressBar.Minimum = 0
        ProgressBar.Maximum = lastRow - 1
        ProgressBar.Value = 0
        ProgressBar.Step = 1

        Dim nSucc%, nFail%, loopCount%
        Dim fExis As Boolean
        nSucc = fileDic.Count

        If colForInsert > 0 Then
            ' 重新遍历一次，插入图片
            For loopCount = 1 To 5
                '
                With Me.ListBox1
                    .Items.Add($"第{loopCount}次插入")
                    .SetSelected(ListBox1.Items.Count - 1, True)
                End With
                '
                For Each k In fileDic.Keys

                    Dim i As Integer = k

                    Dim savePath As String = fileDic(i)
                    fExis = File.Exists(savePath)

                    If Not fExis Then
                        nFail += 1
                    Else
                        fileDic.Remove(i)
                        '
                        l = xlWorkSheet.Cells(i, colForInsert).Left
                        t = xlWorkSheet.Cells(i, colForInsert).Top + 5
                        w = xlWorkSheet.Cells(i, colForInsert).Width
                        h = -1

                        sha = xlWorkSheet.Shapes.AddPicture(savePath, LinkToFile:=False, SaveWithDocument:=True, l, t, -1, -1)
                        sha.LockAspectRatio = True
                        sha.Width = w
                        h = sha.Height
                        If h > maxRowHeight Then
                            h = maxRowHeight - 10
                            sha.Height = h
                        End If
                        Dim targetRow As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.Rows(i)
                        targetRow.RowHeight = h + 10

                        nSucc += 1

                    End If


                    ' 更新进度条
                    ProgressBar.PerformStep()
                    System.Windows.Forms.Application.DoEvents()


                    ' 展示进度 百分比
                    Me.Label2.Text = i & "/" & lastRow & "(" & Microsoft.VisualBasic.Strings.Format(i / lastRow, "0.00%") & ")"
                    With Me.ListBox1

                        .Items.Add($"插入-{fExis}-{i}-{savePath}")
                        .SetSelected(ListBox1.Items.Count - 1, True)
                    End With
                Next
                '
                If fileDic.Count = 0 Then
                    Exit For
                End If
            Next
        End If

        ' 关闭Excel文件

        With xlWorkBook
            .SaveCopyAs(nFile)
            '.SaveAs2(Filename:=nFile, FileFormat:=51) ' 51表示xlsx格式
            .Saved = True
            .Save()
        End With
        xlWorkBook.Close(False)
        xlApp.Quit()

        ' 释放资源
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlApp)

        nFail = fileDic.Count
        nSucc -= nFail

        With Me.ListBox1
            .Items.Add($"完成所有文件插入，成功{nSucc}/失败{nFail}")
            .SetSelected(ListBox1.Items.Count - 1, True)
        End With

        ' 列出失败的文件
        For Each k In fileDic.Keys
            Dim i As Integer = k
            Dim savePath As String = fileDic(i)
            With Me.ListBox1
                .Items.Add($"失败-{i}-{savePath}")
                .SetSelected(ListBox1.Items.Count - 1, True)
            End With
        Next

        ' 弹出完成提示
        MessageBox.Show($"完成:成功{nSucc}/失败{nFail}")
    End Sub

    Private Sub TbSleep_LostFocus(sender As Object, e As EventArgs) Handles TbSleep.LostFocus
        Dim SleepValue As String
        Dim SleepNum As Integer
        SleepValue = Me.TbSleep.Text
        If Len(SleepValue) = 0 Then

        ElseIf IsNumeric(SleepValue) = False Then
            MsgBox("请输入数字")
            Me.TbSleep.Select()
        Else
            SleepNum = Math.Abs(CInt(SleepValue))
            SleepNum = Math.Min(SleepNum, 1000)
            SleepNum = Math.Max(SleepNum, 1)
            Me.TbSleep.Text = SleepNum
        End If
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim file = FileVersionInfo.GetVersionInfo(System.Windows.Forms.Application.ExecutablePath)
        Me.Text = Me.Text & "." & file.FileVersion
    End Sub
End Class